-- This is an example of handling different exceptions.
-- Courtesy of Laszlo Czinkoczki


DECLARE
  TYPE DeptList IS TABLE OF NUMBER;
  v_depts DeptList := DeptList(10, 20,50,90);
  TYPE SalList IS TABLE OF NUMBER;
  v_salaries SalList:= SalList(10000,-1,110,300);
  TYPE JobList IS TABLE OF VARCHAR2(100);
  v_jobs JobList :=  JobList ('IT_PROG','SA_REP','WRONG','ST_CLERK');
  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT (e_bulk_errors, -24381 );

BEGIN
  FORALL i IN v_depts.FIRST..v_depts.LAST
    SAVE EXCEPTIONS
    UPDATE hr.empLOYEES SET SALARY=v_salaries(i), job_id=v_jobs(i) WHERE department_id= v_depts (i);
  EXCEPTION WHEN e_bulk_errors THEN
    DBMS_OUTPUT.PUT_LINE('Number of errors is: '|| SQL%BULK_EXCEPTIONS.COUNT);
    FOR j in 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE (
        TO_CHAR(SQL%BULK_EXCEPTIONS(j).error_index) || ' / ' ||
        SQLERRM(-SQL%BULK_EXCEPTIONS(j).error_code));
    END LOOP;
END;
/
